#!/use/bin/python
# coding=utf-8
# 主动服务 覆盖设备量
import datetime
import pymysql

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# 记录 主动服务-早间开机可见用户数 analysis_feed_number
def insertZjkjSubscribeNumber(date,beforeDate):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_feed_number 表是否已经存在当天 早间开机的数据
        selectCountSql = """
                    select count(1) num from analysis_feed_number t where t.service_key ='zjkj' and t.ref_date =  '%s'
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日早间服务可见数量
        conn1, cur1 = getConfConnection()
        selectNumSql = """
                    select count(1) subNum from life_assistant_device t where t.zjkj_ui =1
                """;
        cur1.execute(selectNumSql)
        resultNum = cur1.fetchone()
        #计算与昨天的可见量差值 作为新增的数量
        selectBeforeCountSql = """
                    select cumulate_num from analysis_feed_number t where t.service_key ='zjkj' and t.ref_date =  '%s'
                """ % beforeDate
        cur.execute(selectBeforeCountSql)
        resultBeforeNum = cur.fetchone()
        newNum = resultNum[0]
        if resultBeforeNum != None:
            newNum = resultNum[0] - resultBeforeNum[0]

        if countNum == 0:
            logDetail = "%s日新增-可见早间服务设备累计量为%s,新增设备量为%s" % (date,resultNum[0],newNum)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_feed_number(new_num,cumulate_num,ref_date,service_key) 
                value(%s,%s,'%s','zjkj')""" % (newNum,resultNum[0],date)
            cur.execute(insertSql)
        else:
            logDetail = "%s日更新-可见早间服务设备累计量为%s,新增设备量为%s" % (date,resultNum[0],newNum)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_feed_number set new_num = %s ,cumulate_num = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='zjkj'
            """ % (newNum,resultNum[0], date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 主动服务-夜间音量可见用户数 analysis_feed_number
def insertYjylSubscribeNumber(date,beforeDate):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_feed_number 表是否已经存在当天 夜间音量的数据
        selectCountSql = """
                    select count(1) num from analysis_feed_number t where t.service_key ='yjyl' and t.ref_date =  '%s'
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日夜间音量可见数量
        conn1, cur1 = getConfConnection()
        selectNumSql = """
                    select count(1) subNum from life_assistant_device t where t.yjyl_ui =1 
                """;
        cur1.execute(selectNumSql)
        resultNum = cur1.fetchone()
        # 计算与昨天的可见量差值 作为新增的数量
        selectBeforeCountSql = """
                            select cumulate_num from analysis_feed_number t where t.service_key ='yjyl' and t.ref_date =  '%s'
                        """ % beforeDate
        cur.execute(selectBeforeCountSql)
        resultBeforeNum = cur.fetchone()
        newNum = resultNum[0]
        if resultBeforeNum != None:
            newNum = resultNum[0] - resultBeforeNum[0]
        if countNum == 0:
            logDetail = "%s日新增-可见夜间音量设备累计量为%s,新增设备量为%s" % (date,resultNum[0],newNum)
            print(logDetail)
            # 插入夜间音量可见设备量
            insertSql = """insert into analysis_feed_number(new_num,cumulate_num,ref_date,service_key) 
                value(%s,%s,'%s','yjyl')""" % (newNum,resultNum[0],date)
            cur.execute(insertSql)
        else:
            logDetail = "%s日更新-可见夜间音量设备累计量为%s,新增设备量为%s" % (date,resultNum[0],newNum)
            print(logDetail)
            # 更新夜间音量可见设备量
            updateSql = """
                update analysis_feed_number set new_num = %s , cumulate_num = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='yjyl'
            """ % (newNum,resultNum[0], date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 主动服务-健康作息可见用户数 analysis_feed_number
def insertJkzxSubscribeNumber(date,beforeDate):
    try:
        conn, cur = getDataConnection()
        # 使用cursor()方法获取操作游标
        cur = conn.cursor()
        #判断 analysis_feed_number 表是否已经存在当天 健康作息的数据
        selectCountSql = """
                    select count(1) num from analysis_feed_number t where t.service_key ='jkzx' and t.ref_date =  '%s'
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日健康作息可见数量
        conn1, cur1 = getConfConnection()
        selectNumSql = """
                    select count(1) subNum from life_assistant_device t where  t.jkzx_ui =1 
                """;
        cur1.execute(selectNumSql)
        resultNum = cur1.fetchone()
        # 计算与昨天的可见量差值 作为新增的数量
        selectBeforeCountSql = """
                                  select cumulate_num from analysis_feed_number t where t.service_key ='jkzx' and t.ref_date =  '%s'
                              """ % beforeDate
        cur.execute(selectBeforeCountSql)
        resultBeforeNum = cur.fetchone()
        newNum = resultNum[0]
        if resultBeforeNum != None:
            newNum = resultNum[0] - resultBeforeNum[0]
        if countNum == 0:
            logDetail = "%s日新增-可见健康作息设备累计量为%s,新增设备量为%s" % (date,resultNum[0],newNum)
            print(logDetail)
            # 插入健康作息可见设备量
            insertSql = """insert into analysis_feed_number(new_num,cumulate_num,ref_date,service_key) 
                value(%s,%s,'%s','jkzx')""" % (newNum,resultNum[0],date)
            cur.execute(insertSql)
        else:
            logDetail = "%s日更新-可见健康作息设备累计量为%s,新增设备量为%s" % (date,resultNum[0],newNum)
            print(logDetail)
            # 更新健康作息可见设备量
            updateSql = """
                update analysis_feed_number set new_num = %s, cumulate_num = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='jkzx'
            """ % (newNum,resultNum[0], date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 主动服务-追剧提醒可见用户数 analysis_feed_number
def insertZjtxSubscribeNumber(date,beforeDate):
    try:
        conn, cur = getDataConnection()
        # 使用cursor()方法获取操作游标
        cur = conn.cursor()
        #判断 analysis_feed_number 表是否已经存在当天 追剧提醒的数据
        selectCountSql = """
                    select count(1) num from analysis_feed_number t where t.service_key ='zjtx' and t.ref_date =  '%s'
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日追剧提醒可见数量
        conn1, cur1 = getConfConnection()
        selectNumSql = """
                    select count(1) subNum from life_assistant_device t where  t.zjtx_ui =1 
                """;
        cur1.execute(selectNumSql)
        resultNum = cur1.fetchone()
        # 计算与昨天的可见量差值 作为新增的数量
        selectBeforeCountSql = """
                                  select cumulate_num from analysis_feed_number t where t.service_key ='zjtx' and t.ref_date =  '%s'
                              """ % beforeDate
        cur.execute(selectBeforeCountSql)
        resultBeforeNum = cur.fetchone()
        newNum = resultNum[0]
        if resultBeforeNum != None:
            newNum = resultNum[0] - resultBeforeNum[0]
        if countNum == 0:
            logDetail = "%s日新增-可见追剧提醒设备累计量为%s,新增设备量为%s" % (date,resultNum[0],newNum)
            print(logDetail)
            # 插入健康作息可见设备量
            insertSql = """insert into analysis_feed_number(new_num,cumulate_num,ref_date,service_key) 
                value(%s,%s,'%s','zjtx')""" % (newNum,resultNum[0],date)
            cur.execute(insertSql)
        else:
            logDetail = "%s日更新-可见追剧提醒设备累计量为%s,新增设备量为%s" % (date,resultNum[0],newNum)
            print(logDetail)
            # 更新健康作息可见设备量
            updateSql = """
                update analysis_feed_number set new_num = %s, cumulate_num = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='zjtx'
            """ % (newNum,resultNum[0], date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 主动服务-3个服务任意服务(总体)可见用户数 analysis_feed_number
def insertAllSubscribeNumber(date,beforeDate):
    try:
        conn, cur = getDataConnection()
        # 使用cursor()方法获取操作游标
        cur = conn.cursor()
        #判断 analysis_feed_number 表是否已经存在当天 总体可见设备量的数据
        selectCountSql = """
                    select count(1) num from analysis_feed_number t where t.service_key ='all' and t.ref_date =  '%s'
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日总体可见设备量
        conn1, cur1 = getConfConnection()
        selectNumSql = """
                    select count(1) subNum from life_assistant_device t where t.zjkj_ui =1
                        or t.jkzx_ui =1 or t.yjyl_ui =1 
                """;
        cur1.execute(selectNumSql)
        resultNum = cur1.fetchone()
        # 计算与昨天的可见量差值 作为新增的数量
        selectBeforeCountSql = """
                                  select cumulate_num from analysis_feed_number t where t.service_key ='all' and t.ref_date =  '%s'
                              """ % beforeDate
        cur.execute(selectBeforeCountSql)
        resultBeforeNum = cur.fetchone()
        newNum = resultNum[0]
        if resultBeforeNum != None:
            newNum = resultNum[0] - resultBeforeNum[0]
        if countNum == 0:
            logDetail = "%s日新增-总体可见设备累计量为%s,新增设备量为%s" % (date,resultNum[0],newNum)
            print(logDetail)
            # 插入总体可见设备量
            insertSql = """insert into analysis_feed_number(new_num,cumulate_num,ref_date,service_key) 
                value(%s,%s,'%s','all')""" % (newNum,resultNum[0],date)
            cur.execute(insertSql)
        else:
            logDetail = "%s日更新-总体可见设备累计量为%s,新增设备量为%s" % (date,resultNum[0],newNum)
            print(logDetail)
            # 更新总体可见设备量
            updateSql = """
                update analysis_feed_number set new_num = %s, cumulate_num = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='all'
            """ % (newNum,resultNum[0], date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 当月 主动服务-早间开机可见用户数 analysis_feed_number
def insertZjkjSubscribeNumberMonth(toDay,toMonthDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_feed_number 表是否已经存在当月 早间开机的数据
        selectCountSql = """
                    select count(1) num from analysis_feed_number t where t.service_key ='zjkj' and t.ref_date =  '%s'
                """ % toMonthDay
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日早间服务可见数量,作为本月的可见数量
        selectNumSql = """
                    select cumulate_num from analysis_feed_number t where ref_date = '%s' and service_key ='zjkj'
                """% toDay;
        cur.execute(selectNumSql)
        resultNum = cur.fetchone()
        #计算当月累计新增的数量
        selectNewCountSql = """
                    select sum(new_num) from analysis_feed_number t  where SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s'   and service_key ='zjkj'
                """ % (toMonthDay,toMonthDay)
        cur.execute(selectNewCountSql)
        resultNewNum = cur.fetchone()
        newNum = resultNewNum[0]
        cumulateNum = resultNum[0];

        if countNum == 0:
            logDetail = "%s月份新增-可见早间服务累计设备累计量为%s,新增设备量%s" % (toMonthDay,cumulateNum,newNum)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_feed_number(new_num,cumulate_num,ref_date,service_key) 
                value(%s,%s,'%s','zjkj')""" % (newNum,cumulateNum,toMonthDay)
            cur.execute(insertSql)
        else:
            logDetail = "%s月份更新-可见早间服务累计设备累计量为%s,新增设备量%s" % (toMonthDay,cumulateNum,newNum)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_feed_number set new_num = %s ,cumulate_num = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='zjkj'
            """ % (newNum,cumulateNum, toMonthDay);
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 当月 主动服务-夜间音量可见用户数 analysis_feed_number
def insertYjylSubscribeNumberMonth(toDay,toMonthDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_feed_number 表是否已经存在当月夜间音量的数据
        selectCountSql = """
                    select count(1) num from analysis_feed_number t where t.service_key ='yjyl' and t.ref_date =  '%s'
                """ % toMonthDay
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日夜间音量可见数量,作为本月的可见数量
        selectNumSql = """
                    select cumulate_num from analysis_feed_number t where ref_date = '%s' and service_key ='yjyl'
                """% toDay;
        cur.execute(selectNumSql)
        resultNum = cur.fetchone()
        #计算当月累计新增的数量
        selectNewCountSql = """
                    select sum(new_num) from analysis_feed_number t  where SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s'   and service_key ='yjyl'
                """ % (toMonthDay,toMonthDay)
        cur.execute(selectNewCountSql)
        resultNewNum = cur.fetchone()
        newNum = resultNewNum[0]
        cumulateNum = resultNum[0];

        if countNum == 0:
            logDetail = "%s月份新增可见夜间音量累计设备累计量为%s,新增设备量%s" % (toMonthDay,cumulateNum,newNum)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_feed_number(new_num,cumulate_num,ref_date,service_key) 
                value(%s,%s,'%s','yjyl')""" % (newNum,cumulateNum,toMonthDay)
            cur.execute(insertSql)
        else:
            logDetail = "%s月份更新可见夜间音量累计设备累计量为%s,新增设备量%s" % (toMonthDay,cumulateNum,newNum)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_feed_number set new_num = %s ,cumulate_num = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='yjyl'
            """ % (newNum,cumulateNum, toMonthDay);
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 当月 主动服务-健康作息可见用户数 analysis_feed_number
def insertJkzxSubscribeNumberMonth(toDay,toMonthDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_feed_number 表是否已经存在当月健康作息的数据
        selectCountSql = """
                    select count(1) num from analysis_feed_number t where t.service_key ='jkzx' and t.ref_date =  '%s'
                """ % toMonthDay
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日健康作息可见数量,作为本月的可见数量
        selectNumSql = """
                    select cumulate_num from analysis_feed_number t where ref_date = '%s' and service_key ='jkzx'
                """% toDay;
        cur.execute(selectNumSql)
        resultNum = cur.fetchone()
        #计算当月累计新增的数量
        selectNewCountSql = """
                    select sum(new_num) from analysis_feed_number t  where SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s'   and service_key ='jkzx'
                """ % (toMonthDay,toMonthDay)
        cur.execute(selectNewCountSql)
        resultNewNum = cur.fetchone()
        newNum = resultNewNum[0]
        cumulateNum = resultNum[0];

        if countNum == 0:
            logDetail = "%s月份新增可见健康作息累计设备累计量为%s,新增设备量%s" % (toMonthDay,cumulateNum,newNum)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_feed_number(new_num,cumulate_num,ref_date,service_key) 
                value(%s,%s,'%s','jkzx')""" % (newNum,cumulateNum,toMonthDay)
            cur.execute(insertSql)
        else:
            logDetail = "%s月份更新可见健康作息累计设备累计量为%s,新增设备量%s" % (toMonthDay,cumulateNum,newNum)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_feed_number set new_num = %s ,cumulate_num = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='jkzx'
            """ % (newNum,cumulateNum, toMonthDay);
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 当月 主动服务-追剧提醒可见用户数 analysis_feed_number
def insertZjtxSubscribeNumberMonth(toDay,toMonthDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_feed_number 表是否已经存在当月追剧提醒的数据
        selectCountSql = """
                    select count(1) num from analysis_feed_number t where t.service_key ='zjtx' and t.ref_date =  '%s'
                """ % toMonthDay
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日追剧提醒可见数量,作为本月的可见数量
        selectNumSql = """
                    select cumulate_num from analysis_feed_number t where ref_date = '%s' and service_key ='zjtx'
                """% toDay;
        cur.execute(selectNumSql)
        resultNum = cur.fetchone()
        #计算当月累计新增的数量
        selectNewCountSql = """
                    select sum(new_num) from analysis_feed_number t  where SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s'   and service_key ='zjtx'
                """ % (toMonthDay,toMonthDay)
        cur.execute(selectNewCountSql)
        resultNewNum = cur.fetchone()
        newNum = 0
        if resultNewNum!= None:
            newNum = resultNewNum[0]
        cumulateNum = 0
        if resultNum!= None:
            cumulateNum = resultNum[0];

        if countNum == 0:
            logDetail = "%s月份新增可见追剧提醒累计设备累计量为%s,新增设备量%s" % (toMonthDay,cumulateNum,newNum)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_feed_number(new_num,cumulate_num,ref_date,service_key) 
                value(%s,%s,'%s','zjtx')""" % (newNum,cumulateNum,toMonthDay)
            cur.execute(insertSql)
        else:
            logDetail = "%s月份更新可见追剧提醒累计设备累计量为%s,新增设备量%s" % (toMonthDay,cumulateNum,newNum)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_feed_number set new_num = %s ,cumulate_num = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='zjtx'
            """ % (newNum,cumulateNum, toMonthDay);
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 当月 主动服务--3个服务任意服务(总体)可见用户数 analysis_feed_number
def insertAllSubscribeNumberMonth(toDay,toMonthDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_feed_number 表是否已经存在当月-3个服务任意服务(总体)的数据
        selectCountSql = """
                    select count(1) num from analysis_feed_number t where t.service_key ='all' and t.ref_date =  '%s'
                """ % toMonthDay
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日主动服务可见数量,作为本月可见数
        selectNumSql = """
                    select cumulate_num from analysis_feed_number t where ref_date = '%s' and service_key ='all'
                """% toDay;
        cur.execute(selectNumSql)
        resultNum = cur.fetchone()
        #计算当月累计新增的数量
        selectNewCountSql = """
                    select sum(new_num) from analysis_feed_number t  where SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s'   and service_key ='all'
                """ % (toMonthDay,toMonthDay)
        cur.execute(selectNewCountSql)
        resultNewNum = cur.fetchone()
        newNum = resultNewNum[0]
        cumulateNum = resultNum[0];

        if countNum == 0:
            logDetail = "%s月份新增可见生活助手累计设备累计量为%s,新增设备量%s" % (toMonthDay,cumulateNum,newNum)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_feed_number(new_num,cumulate_num,ref_date,service_key) 
                value(%s,%s,'%s','all')""" % (newNum,cumulateNum,toMonthDay)
            cur.execute(insertSql)
        else:
            logDetail = "%s月份更新可见生活助手累计设备累计量为%s,新增设备量%s" % (toMonthDay,cumulateNum,newNum)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_feed_number set new_num = %s ,cumulate_num = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='all'
            """ % (newNum,cumulateNum, toMonthDay);
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getMonthdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m')
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 27
        jobName = '核心指标-生活助手-覆盖用户数'
        tableName = 'analysis_feed_number'
        # 插入日志表
        saveLogSql = """insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                value(%s,'%s','%s','%s','%s')""" % (jobId,jobName,detail,tableName,ref_date)
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    #today = todayYMD()
    date = getdate(1)
    beforeDate = getdate(2)
    monthdate = getMonthdate(1)
    #today = "2021-12-13"
    print ("%s 日期,更新<主动服务 覆盖设备量>报表" % date)
    insertZjkjSubscribeNumber(date,beforeDate)
    insertYjylSubscribeNumber(date,beforeDate)
    insertJkzxSubscribeNumber(date,beforeDate)
    insertZjtxSubscribeNumber(date,beforeDate)
    insertAllSubscribeNumber(date,beforeDate)
    insertZjkjSubscribeNumberMonth(date,monthdate)
    insertYjylSubscribeNumberMonth(date, monthdate)
    insertJkzxSubscribeNumberMonth(date, monthdate)
    insertZjtxSubscribeNumberMonth(date, monthdate)
    insertAllSubscribeNumberMonth(date, monthdate)
